package aes.server;

import java.sql.*;
import java.util.ArrayList;

import common.*;

/**
 * This class represents manager used in order to manage exams
 *
 */
public class ExamsManager {

	/**
	 * Retrieves Exam from the DB by ID
	 * @param ExamID Exam ID
	 * @return Exam object
	 */
	public static Exam getExam(int ExamID) {
		DBManager db = DBManager.getInstance();
		try {
			String SQL = "SELECT e.eId, e.solveTime, e.tComments, e.eComments, e.teacherID, "+
					"u.lastname, u.firstname, d.dName, c.cName "+
					"FROM exams e, users u, departments d , courses c "+
					"WHERE e.teacherId=u.id AND CAST((e.eId/10000) AS SIGNED)=d.dId "+ 
					"AND CAST((e.eId%10000)/100 AS SIGNED)=c.cId AND e.eId=?;";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(SQL);

			stmt1.setInt(1, ExamID);
			ResultSet rs1 = stmt1.executeQuery();
			if (rs1.next()) {
				// Create Exam.
				Exam ret = new Exam();
				ret.setId(ExamID);
				ret.setId(rs1.getInt("eId"));
				ret.setSolveTime(rs1.getInt("solveTime"));
				ret.setNotesForTeacher(rs1.getString("tComments"));
				ret.setNotesForStudent(rs1.getString("eComments"));
				ret.setTeacherID(rs1.getInt("teacherID"));
				ret.setSubjectName(rs1.getString("dName"));
				ret.setCourseName(rs1.getString("cName"));


				// Get questions.
				ret.setQuestions(QuestionsManager.getExamQuestions(ExamID));
				rs1.close();
				return ret;
			}
			rs1.close();
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Get exams list written by same teacher
	 * @param Teacher id
	 * @return Exams list written by same teacher
	 */
	public static Exam[] getExamsByTeacher(int tID){
		ArrayList<Exam> arr=new ArrayList<Exam>();
		DBManager db = DBManager.getInstance();
		try {
			String sql ="SELECT e.eId, e.solveTime, e.tComments, e.eComments, e.teacherID, "+
					"u.lastname, u.firstname, d.dName, c.cName "+
					"FROM exams e, users u, departments d , courses c "+
					"WHERE e.teacherId=u.id AND CAST((e.eId/10000) AS SIGNED)=d.dId "+ 
					"AND CAST((e.eId%10000)/100 AS SIGNED)=c.cId AND e.teacherId=?";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, tID);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				Exam ex = new Exam();
				ex.setId(rs1.getInt("eId"));
				ex.setSolveTime(rs1.getInt("solveTime"));
				ex.setNotesForTeacher(rs1.getString("tComments"));
				ex.setNotesForStudent(rs1.getString("eComments"));
				ex.setTeacherID(rs1.getInt("teacherID"));
				ex.setSubjectName(rs1.getString("dName"));
				ex.setCourseName(rs1.getString("cName"));
				ex.setQuestions(QuestionsManager.getExamQuestions(ex.getId()));
				arr.add(ex);
			}
			rs1.close();
			Exam[] ret = new Exam[arr.size()];
			arr.toArray(ret);
			return ret;
		}
		catch(SQLException ex1){
			return null;
		}
	}

	/**
	 * Get exams list written by all teachers
	 * @return Exams list written by all teachers
	 */
	public static Exam[] getExams(){
		ArrayList<Exam> arr=new ArrayList<Exam>();
		DBManager db = DBManager.getInstance();
		try {
			String sql ="SELECT e.eId, e.solveTime, e.tComments, e.eComments, e.teacherID, "+
					"u.lastname, u.firstname, d.dName, c.cName "+
					"FROM exams e, users u, departments d , courses c "+
					"WHERE e.teacherId=u.id AND CAST((e.eId/10000) AS SIGNED)=d.dId "+ 
					"AND CAST((e.eId%10000)/100 AS SIGNED)=c.cId";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				Exam ex = new Exam();
				ex.setId(rs1.getInt("eId"));
				ex.setSolveTime(rs1.getInt("solveTime"));
				ex.setNotesForTeacher(rs1.getString("tComments"));
				ex.setNotesForStudent(rs1.getString("eComments"));
				ex.setTeacherID(rs1.getInt("teacherID"));
				ex.setSubjectName(rs1.getString("dName"));
				ex.setCourseName(rs1.getString("cName"));
				ex.setQuestions(QuestionsManager.getExamQuestions(ex.getId()));
				arr.add(ex);
			}
			rs1.close();
			Exam[] ret = new Exam[arr.size()];
			arr.toArray(ret);
			return ret;
		}
		catch(SQLException ex1){
			return null;
		}
	}
	
	/**
	 * Get exams list from DB
	 * @return All exams on DB
	 */
	public static Exam[] getExamsList(){
		ArrayList<Exam> arr=new ArrayList<Exam>();
		DBManager db = DBManager.getInstance();
		try {
			String sql ="select eId, solveTime, tComments, eComments, teacherID" +
					"From exams Order by teacherID";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				Exam ex = new Exam();
				ex.setId(rs1.getInt("eId"));
				ex.setSolveTime(rs1.getInt("solveTime"));
				ex.setNotesForTeacher(rs1.getString("tComments"));
				ex.setNotesForStudent(rs1.getString("eComments"));
				ex.setTeacherID(rs1.getInt("teacherID"));
				ex.setQuestions(QuestionsManager.getExamQuestions(ex.getId()));
				arr.add(ex);
			}
			rs1.close();
			Exam[] ret = new Exam[arr.size()];
			arr.toArray(ret);
			return ret;
		}
		catch(SQLException ex){
			return null;
		}
	}

	/**
	 * Get exams list from DB by course and subject
	 * @param subject Id of the subject
	 * @param course Id of the course
	 * @return All exams of specified subject and course
	 */
	public static Exam[] getExamsList(int subject, int course){
		ArrayList<Exam> arr=new ArrayList<Exam>();
		DBManager db = DBManager.getInstance();
		try {
			String sql ="SELECT eId, solveTime, tComments, eComments, teacherID " +
					"FROM exams e, departments d , courses c" +
					"WHERE CAST((e.eId/10000) AS SIGNED)=d.dId AND CAST((e.eId%10000)/100 AS SIGNED)=c.cId " + 
					" AND d.dId=? AND c.cId=? " +
					"ORDER BY eId";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, subject);
			stmt1.setInt(2, course);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				Exam ex = new Exam();
				ex.setId(rs1.getInt("eId"));
				ex.setSolveTime(rs1.getInt("solveTime"));
				ex.setNotesForTeacher(rs1.getString("tComments"));
				ex.setNotesForStudent(rs1.getString("eComments"));
				ex.setTeacherID(rs1.getInt("teacherID"));
				ex.setQuestions(QuestionsManager.getExamQuestions(ex.getId()));
				arr.add(ex);
			}
			rs1.close();
			Exam[] ret = new Exam[arr.size()];
			arr.toArray(ret);
			return ret;
		}
		catch(SQLException ex){
			return null;
		}
	}

	/**
	 * Add new exam to DB
	 * @param Exam
	 * @return true-exam addition succeed, false-exam addition failed
	 */
	public static boolean addExam(Exam ex){
		DBManager db = DBManager.getInstance();
		try {
			String sql ="INSERT INTO exams (`eId`,`solveTime`,`tComments`,`eComments`,`teacherID`) "+
					"VALUES(?,?,?,?,?);";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, ex.getId());
			stmt1.setInt(2, ex.getSolveTime());
			stmt1.setString(3, ex.getNotesForTeacher());
			stmt1.setString(4, ex.getNotesForStudent());
			stmt1.setInt(5, ex.getTeacherID());
			stmt1.executeUpdate();

			String sql2 ="DELETE FROM question_in_exam "+
					"WHERE eId=?;";
			PreparedStatement stmt2 = db.getConnection().prepareStatement(sql2);
			stmt2.setInt(1, ex.getId());
			stmt2.executeUpdate();

			if(ex.getQuestions()==null || ex.getQuestions().length<1){
				return true;
			}
			String sql3="INSERT INTO question_in_exam (`eId`,`qId`,`points`) "+
					"VALUES(?,?,?);";
			PreparedStatement stmt3 = db.getConnection().prepareStatement(sql3);
			for(Question q:ex.getQuestions()){
				if(q.getPoints()>100 || q.getPoints()<1)
					continue;
				stmt3.setInt(1, ex.getId());
				stmt3.setInt(2, q.getId());
				stmt3.setInt(3, q.getPoints());
				stmt3.executeUpdate();
			}
			return true;
		}
		catch(SQLException ex1){
			return false;
		}

	}

	/**
	 * Remove exam from DB
	 * @param Exam ID
	 * @return true-delete exam succeed, false-delete exam failed
	 */
	public static boolean removeExam(int eID){
		DBManager db = DBManager.getInstance();
		try {
			String sql ="DELETE FROM exams "+
					"WHERE eId=?;";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, eID);
			stmt1.executeUpdate();

			String sql2 ="DELETE FROM question_in_exam "+
					"WHERE eId=?;";
			PreparedStatement stmt2 = db.getConnection().prepareStatement(sql2);
			stmt2.setInt(1, eID);
			stmt2.executeUpdate();
			return true;
		}
		catch(SQLException ex){
			return false;
		}

	}

	/**
	 * Update exam DB
	 * @param Exam
	 * @return true-update succeed, false-update failed
	 */
	public static boolean updateExam(Exam ex){

		DBManager db = DBManager.getInstance();
		try {
			String sql ="UPDATE exams "+
					"SET `solveTime` = ?, `tComments` = ?, `eComments` = ?, `teacherID` = ? WHERE eId=?;";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, ex.getSolveTime());
			stmt1.setString(2, ex.getNotesForTeacher());
			stmt1.setString(3, ex.getNotesForStudent());
			stmt1.setInt(4, ex.getTeacherID());
			stmt1.setInt(5, ex.getId());
			stmt1.executeUpdate();

			String sql2 ="DELETE FROM question_in_exam "+
					"WHERE eId=?;";
			PreparedStatement stmt2 = db.getConnection().prepareStatement(sql2);
			stmt2.setInt(1, ex.getId());
			stmt2.executeUpdate();

			if(ex.getQuestions()==null || ex.getQuestions().length<1){
				return true;
			}
			String sql3="INSERT INTO question_in_exam (`eId`,`qId`,`points`) "+
					"VALUES(?,?,?);";
			PreparedStatement stmt3 = db.getConnection().prepareStatement(sql3);
			for(Question q:ex.getQuestions()){
				if(q == null || q.getPoints()>100 || q.getPoints()<1)
					continue;
				stmt3.setInt(1, ex.getId());
				stmt3.setInt(2, q.getId());
				stmt3.setInt(3, q.getPoints());
				stmt3.executeUpdate();
			}

			return true;
		}
		catch(SQLException ex1){
			return false;

		}

	}

	/**
	 * ADD question to exam
	 * @param Question we wont to add and Exam Id 
	 * @param eID Exam Id
	 * @return true- addition succeed, false- addition failed
	 */
	public static boolean addQuestionToExam(Question q,int eID){
		Exam ex = new Exam();
		ex=getExam(eID);
		if(ex==null || q==null)
			return false;

		DBManager db = DBManager.getInstance();
		String sql ="DELETE FROM question_in_exam "+
				"WHERE eId=? AND qId=?;";
		PreparedStatement stmt;
		try {
			stmt = db.getConnection().prepareStatement(sql);
			stmt.setInt(1,eID);
			stmt.setInt(2,q.getId());
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		String sql1="INSERT INTO question_in_exam (`eId`,`qId`,`points`) "+
				"VALUES(?,?,?);";
		PreparedStatement stmt1;
		try {
			stmt1 = db.getConnection().prepareStatement(sql1);
			stmt1.setInt(1, eID);
			stmt1.setInt(2, q.getId());
			stmt1.setInt(3, q.getPoints());
			stmt1.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		return true;      

	}

	/**
	 * Remove question from DB
	 * @param Queston id (that we want to remove)
	 * @param Exam id (of which we want to remove question)
	 * @return true- remove succeed, false- remove failed
	 */
	public static boolean removeQuestionFromExam(int qID,int exID){	
		DBManager db = DBManager.getInstance();
		String sql ="DELETE FROM question_in_exam "+
				"WHERE eId=? AND qId=?;";
		PreparedStatement stmt;
		try {
			stmt = db.getConnection().prepareStatement(sql);
			stmt.setInt(1,exID);
			stmt.setInt(2,qID);
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		return true;	

	}

	/**
	 * Get list of questions (from the same subject) that not exist in exam
	 * @param Exam id 
	 * @return Questions list 
	 */
	public static Question[] getNotExistentQuestions(int exID){
		ArrayList<Question> arr=new ArrayList<Question>();
		DBManager db = DBManager.getInstance();		
		String sql ="SELECT * "+
				"FROM aes.questions q, aes.departments d, aes.users u "+
				"WHERE q.teacherID=u.id AND CAST((q.qId/1000) AS SIGNED)=d.dId AND qId not in "+
				"(SELECT qId FROM aes.question_in_exam "+  
				"WHERE eId=?);";
		try {
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setInt(1, exID);
			ResultSet rs1 = stmt.executeQuery();
			while(rs1.next()){
				Question q = new Question();
				q.setId(rs1.getInt("qId"));
				q.setQuestion(rs1.getString("question"));
				q.setAnswers(new String[]{rs1.getString("answer1"),rs1.getString("answer2"),rs1.getString("answer3"),rs1.getString("answer4")});
				q.setCorrectAnswer(rs1.getInt("correct"));
				q.setTeacherId(rs1.getInt("teacherID"));
				q.setTeacherName(rs1.getString("firstname")+rs1.getString("lastname"));
				q.setNotes(rs1.getString("notes"));
				arr.add(q);
			}

			rs1.close();
			Question[] ret = new Question[arr.size()];
			arr.toArray(ret);
			return ret;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Get from DB exams id list
	 * @param Course id
	 * @param Subject id
	 * @return List of exams ids
	 */
	public static Integer[] getExamsIDsList(int cid, int sid){

		ArrayList<Integer> arr=new ArrayList<Integer>();
		int i;
		DBManager db = DBManager.getInstance();		
		String sql="SELECT eId " +
				"FROM aes.exams " +
				"WHERE CAST((eId/10000) AS SIGNED) = ? AND CAST((eId%10000)/100 AS SIGNED)=? ";
		try {
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setInt(1, sid);
			stmt.setInt(2, cid);
			ResultSet rs1 = stmt.executeQuery();
			while(rs1.next()){
				i=rs1.getInt("eId");
				arr.add(i);
			}

			rs1.close();
			Integer[] ret = new Integer[arr.size()];
			arr.toArray(ret);
			return ret;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}




	}

}